<?php 
require_once ("DB.php");

function service_dictionary_create($dictionary) {
    $conn = getDBConnection();
    $conn->query("insert into dictionaries(title, description, creator, create_date) value(?, ?, ?, ?)",
         array($dictionary['title'], $dictionary['description'], $dictionary['creator'], date('Y-m-d H:i:s',
         time())));
    $result = $conn->query("select last_insert_id()");
    if ($row = &$result->fetchRow(DB_FETCHMODE_ASSOC)) {
        $set = $row;
    }
    $serial = $set['last_insert_id()'];
    $result->free();
    $conn->disconnect();
    return $serial;
}
function service_dictionary_getBySerial($serial) {
    $conn = getDBConnection();
    $set = array();
    $result = $conn->query("select * from dictionaries where serial = ? limit 1", array($serial));
    while ($row = &$result->fetchRow(DB_FETCHMODE_ASSOC)) {
        $set = $row;
    }
	$result->free();
    $conn->disconnect();
    if (count($set) > 0) {
        return $set;
    } else {
        return null;
    }
}
function service_dictionary_getByCreator($creator, $orderby = "create_date desc") {
	$conn = getDBConnection();
    $set = array();
    $result = $conn->query("select * from dictionaries where creator = ? order by $orderby", array($creator));
    while ($row = &$result->fetchRow(DB_FETCHMODE_ASSOC)) {
        array_push($set, $row);
    }
	$result->free();
    $conn->disconnect();
    if (count($set) > 0) {
        return $set;
    } else {
        return null;
    }
}
function service_dictionary_getTermsCount($serial, $category = "") {
	$conn = getDBConnection();
	$variables = array($serial);
	if($category != "") {
		$categorySyntax = " and lower(substring(term,1,1)) = ?";
		array_push($variables, $category);
	}
    $result = $conn->query("select count(serial) from dictionary_terms where belongto = ?".$categorySyntax, $variables);
    $set = 0;
    if ($row = &$result->fetchRow(DB_FETCHMODE_ASSOC)) {
        $set = $row['count(serial)'];
    }
    $result->free();
    $conn->disconnect();
    return $set;
}
function service_dictionary_getCount() {
    $conn = getDBConnection();
    $result = $conn->query("select count(serial) from dictionaries");
    $set = 0;
    if ($row = &$result->fetchRow(DB_FETCHMODE_ASSOC)) {
        $set = $row['count(serial)'];
    }
    $result->free();
    $conn->disconnect();
    return $set;
}
function service_dictionary_getAll($start, $size, $orderBy = "create_date desc") {
    $conn = getDBConnection();
    $result = $conn->query("select * from dictionaries order by ".$orderBy." limit ?,? ", array($start, $size));
    $set = array();
    while ($row = &$result->fetchRow(DB_FETCHMODE_ASSOC)) {
        array_push($set, $row);
    }
    $result->free();
    $conn->disconnect();
    return $set;
}
function service_dictionary_update($dictionary) {
    $conn = getDBConnection();
    $conn->query("update dictionaries set title=?,
									description=?,
									last_editor=?,
									last_edit_date=?
									where serial = ? limit 1", array($dictionary['title'], $dictionary['description'],
									     $dictionary['last_editor'], date('Y-m-d H:i:s', time()), $dictionary['serial']));
    $conn->disconnect();
}
function service_dictionary_deleteBySerial($serial) {
    $conn = getDBConnection();
    $conn->query("delete from dictionary_term_definitions 
		where term in (select serial from dictionary_terms where belongto = ?)", array($serial));
    $conn->query("delete from dictionary_conflict_definitions 
		where term in (select serial from dictionary_terms where belongto = ?)", array($serial));
    $conn->query("delete from dictionary_terms where belongto = ?", array($serial));
    $conn->query("delete from dictionary_sources where dictionary = ?", array($serial));
    $conn->query("delete from dictionaries where serial = ? limit 1", array($serial));
	
    $conn->disconnect();
}
function service_dictionary_getSources($serial) {
	$conn = getDBConnection();
	$result = $conn->query("select * from dictionary_sources where dictionary = ?", array($serial));
	$set = array();
	while ($row = &$result->fetchRow(DB_FETCHMODE_ASSOC)) {
        array_push($set, $row['source']);
    }
	$result->free();
	$conn->disconnect();
	return $set;
}
function service_dictionary_addSources($dictionary, $sources) {
	if(!count($sources)) return;
	$conn = getDBConnection();
	foreach($sources as $source) {
    	$conn->query("insert into dictionary_sources(dictionary, source) value(?, ?)", array($dictionary, $source));
	}
	global $featureManager;
	
	$term_num = 0;
	$definition_num = 0;
	
	$context = array();
	$context['sources'] = $sources;
	$featureManager->apply("dictionary.get.term.by.source", $context);
	foreach($sources as $source) {
		foreach($context['terms'][$source] as $term) {
			$term_num += service_dictionary_addTermAndDefinition($term['term'], $term['definition'], $dictionary, $term['version'], $source);
			$definition_num++;
		}
	}
	$conn->disconnect();
	return array('term'=>$term_num, 'definition'=>$definition_num);
}
function service_dictionary_isTermExist($term, $dictionary) {
	$conn = getDBConnection();
	$term_exist = NULL;
	$result = $conn->query("select serial from dictionary_terms where term=? and belongto=? limit 1", array($term, $dictionary));
	if ($row = &$result->fetchRow(DB_FETCHMODE_ASSOC)) {
        $term_exist = $row['serial'];
    }
	$result->free();
	$conn->disconnect();
	return $term_exist;
}
function service_dictionary_addTermAndDefinition($term, $definition, $dictionary, $version, $source = NULL) {
	global $currentUser;
	$conn = getDBConnection();
	$term_exist = service_dictionary_isTermExist($term, $dictionary);
	
	$is_add_term = $term_exist?0:1;
	
	if(!$term_exist) {
		$conn->query("insert into dictionary_terms(term, creator, create_date, belongto) value(?, ?, ?, ?)", array($term, $currentUser['serial'], date('Y-m-d H:i:s', time()), $dictionary));
		$result = $conn->query("select last_insert_id()");
	    if ($row = &$result->fetchRow(DB_FETCHMODE_ASSOC)) {
	        $set = $row;
	    }
	    $term_exist = $set['last_insert_id()'];
	    $result->free();
	}
	$conn->query("insert into dictionary_term_definitions(term, definition, comefrom, inorder, version) value(?, ?, ?, ?, ?)", array($term_exist, $definition, $source, 0, $version));
	$conn->disconnect();
	return $is_add_term;
}
function service_dictionary_deleteSources($dictionary, $sources) {
	if(!count($sources)) return;
	$conn = getDBConnection();
	$definition = 0;
	if(count($sources) == 1) {
		$conn->query("delete from dictionary_sources where dictionary=? and source=?", array($dictionary, $sources[0]));
		$conn->query("delete from dictionary_term_definitions where comefrom=?", array($sources[0]));
		$definition = $conn->affectedRows();
	}
	else {
		$whereSyntax = "in (";
		foreach ($sources as $source) {
            $whereSyntax .= "?, ";
        }
        $whereSyntax = substr($whereSyntax, 0, strlen($whereSyntax) - 2);
        $whereSyntax .= ")";
		array_unshift($sources, $dictionary);
    	$conn->query("delete from dictionary_sources where dictionary=? and source $whereSyntax", $sources);
		$conn->query("delete from dictionary_term_definitions where comefrom $whereSyntax", array($sources));
		$definition = $conn->affectedRows();
	}
	
	$conn->disconnect();
	return $definition;
}
function service_dictionary_refreshSources($dictionary, $sources) {
	if(!count($sources)) return;
	global $featureManager;
	$context = array();
	$context['sources'] = $sources;
	$featureManager->apply("dictionary.get.term.by.source", $context);
	foreach($sources as $source) {
		$terms = array();
		foreach($context['terms'][$source] as $term) {
			service_dictionary_refreshDefinition($term['term'], $term['definition'], $dictionary, $term['version'], $source);
			array_push($terms, $term['term']);
		}
		$definitions_of_source = service_dictionary_getDefinitionsByDictAndSource($dictionary, $source);
		foreach($definitions_of_source as $definition) {
			$term_of_def = service_dictionary_getTermBySerial($definition['term']);
			if(!in_array($term_of_def['term'], $terms)) service_dictionary_addConflictDefinition($definition['term'], "", $source, 0);
		}
	}
}
function service_dictionary_refreshDefinition($term, $definition, $dictionary, $version, $source) {
	global $currentUser;
	$conn = getDBConnection();
	$term_exist = service_dictionary_isTermExist($term, $dictionary);
	
	if(!$term_exist) {
		$conn->query("insert into dictionary_terms(term, creator, create_date, belongto) value(?, ?, ?, ?)", array($term, $currentUser['serial'], date('Y-m-d H:i:s', time()), $dictionary));
		$conn->query("insert into dictionary_term_definitions(term, definition, comefrom, inorder, version) value(?, ?, ?, ?, ?)", array($term_exist, $definition, $source, 0, $version));
		$result = $conn->query("select last_insert_id()");
	    if ($row = &$result->fetchRow(DB_FETCHMODE_ASSOC)) {
	        $set = $row;
	    }
	    $term_exist = $set['last_insert_id()'];
	    $result->free();
	} else {
		$version_of_dictionary = 0;
		$result = $conn->query("select * from dictionary_term_definitions where term=? and comefrom=? limit 1", array($term_exist, $source));
		if ($row = &$result->fetchRow(DB_FETCHMODE_ASSOC)) {
	        $version_of_dictionary = $row['version'];
	    }
		if($version > $version_of_dictionary) service_dictionary_addConflictDefinition($term_exist, $definition, $source, $version);
	}
	$result->free();
	$conn->disconnect();
}
function service_dictionary_getTerms($dictionary, $category = "", $getConflict, $start, $size, $orderBy = "term") {
    $conn = getDBConnection();
    
    $variables = array($dictionary, $start, $size);
    if ($category != "") {
        $categorySyntax = " and lower(substring(term,1,1)) = ?";
        array_splice($variables, 1, 0, $category);
    }
    
    $set = array();
    $result = $conn->query("select * from dictionary_terms where belongto = ?".$categorySyntax." order by ".$orderBy." limit ?,?",
         $variables);
    while ($row = &$result->fetchRow(DB_FETCHMODE_ASSOC)) {
        $row['definition'] = service_dictionary_getDefinition($row['serial']);
        foreach ($row['definition'] as & $definition) {
        	if($definition['mergedby']) {
        		$row['definition'][$definition['mergedby']]['comefrom'] .= ",".$definition['comefrom'];
				unset($row['definition'][$definition['serial']]);
        	}
		}
		if($getConflict) {
	        $conflict_definitions = service_dictionary_getConflictDefinition($row['serial']);
			foreach ($row['definition'] as & $definition) {
				$comefroms = explode(',', $definition['comefrom']);
				foreach($comefroms as $comefrom) {
					if (array_key_exists($comefrom, $conflict_definitions)) $definition['conflict_definition'] = array_merge((array)$definition['conflict_definition'], $conflict_definitions[$comefrom]);
				}
			}
		}
        array_push($set, $row);
    }
	$result->free();
    $conn->disconnect();
    if (count($set) > 0) {
        return $set;
    } else {
        return null;
    }
}


function service_dictionary_getTermBySerial($serial) {
	$conn = getDBConnection();
	
	$result = $conn->query("select * from dictionary_terms where serial = ? limit 1", array($serial));
    $conn->disconnect();
    if ($row = &$result->fetchRow(DB_FETCHMODE_ASSOC)) {
    	$result->free();
        return $row;
    } else return NULL;
}
function service_dictionary_deleteTermBySerial($serial) {
	$conn = getDBConnection();
    $result = $conn->query("delete from dictionary_term_definitions 
		where term = ?", array($serial));
    $result = $conn->query("delete from dictionary_conflict_definitions 
		where term = ?", array($serial));
    $result = $conn->query("delete from dictionary_terms where serial = ?", array($serial));
	$result->free();
    $conn->disconnect();
}
function service_dictionary_getDefinitionsByDictAndSource($dictionary, $source){
	$conn = getDBConnection();
	$terms = service_dictionary_getTerms($dictionary, "", false, 0, 10000);
	$terms_serial = array();
	$whereSyntax = "in (";
	foreach($terms as $term) {
        $whereSyntax .= "?, ";
		array_push($terms_serial, $term['serial']);
	}
    $whereSyntax = substr($whereSyntax, 0, strlen($whereSyntax) - 2);
    $whereSyntax .= ")";
	array_push($terms_serial, $source);
	
	$set = array();
	$result = $conn->query("select * from dictionary_term_definitions where term $whereSyntax and comefrom = ?", $terms_serial);
	while ($row = &$result->fetchRow(DB_FETCHMODE_ASSOC)) {
        array_push($set, $row);
    }
	$result->free();
	$conn->disconnect();
	return $set;
}
function service_dictionary_getDefinition($serial) {
	$conn = getDBConnection();
	$set = array();
	$result = $conn->query("select * from dictionary_term_definitions where term = ? order by inorder", $serial);
	while ($row = &$result->fetchRow(DB_FETCHMODE_ASSOC)) {
        $set[$row['serial']] = $row;
    }
	$result->free();
	$conn->disconnect();
	return $set;
}
function service_dictionary_alterDefinition($term, $comefrom, $definition, $version) {
	$conn = getDBConnection();
	$conn->query("update dictionary_term_definitions set definition=?, version=?
									where term = ? and comefrom = ? limit 1", array($definition, $version, $term, $comefrom));
	$conn->disconnect();
}
function service_dictionary_dropDefinition($term, $comefrom) {
	$conn = getDBConnection();
	$conn->query("delete from dictionary_term_definitions where term = ? and comefrom = ? limit 1", array($term, $comefrom));
	$conn->disconnect();
}
function service_dictionary_getConflictDefinition($serial) {
	$conn = getDBConnection();
	$set = array();
	$result = $conn->query("select * from dictionary_conflict_definitions where term = ?", $serial);
	while ($row = &$result->fetchRow(DB_FETCHMODE_ASSOC)) {
        $set[$row['comefrom']][] = $row;
    }
	$result->free();
	$conn->disconnect();
	return $set;
}
function service_dictionary_addConflictDefinition($term, $definition, $source, $version) {
	$conn = getDBConnection();
	$conn->query("insert into dictionary_conflict_definitions(term, definition, comefrom, version) value(?, ?, ?, ?)", array($term, $definition, $source, $version));
	$conn->disconnect();
}
function service_dictionary_discardConflict($serial) {
	$conn = getDBConnection();
	$conn->query("delete from dictionary_conflict_definitions where serial=? limit 1", $serial);
	$conn->disconnect();
}
function service_dictionary_adoptConflict($serial) {
	$conn = getDBConnection();
	$result = $conn->query("select * from dictionary_conflict_definitions where serial=? limit 1", $serial);
	$conflict = &$result->fetchRow(DB_FETCHMODE_ASSOC);
	if($conflict['definition'] == "")
		service_dictionary_dropDefinition($conflict['term'], $conflict['comefrom']);
	else 
		service_dictionary_alterDefinition($conflict['term'], $conflict['comefrom'], $conflict['definition'], $conflict['version']);
	
	$conn->query("delete from dictionary_conflict_definitions where serial=?", $serial);
	$result->free();
	$conn->disconnect();
	return $conflict['definition'];
}
function service_dictionary_merge_definition($from, $to) {
	$conn = getDBConnection();
	$conn->query("update dictionary_term_definitions set mergedby=? where serial=? limit 1", array($to, $from));
	$conn->disconnect();
}
function service_dictionary_split_definition($mergedto, $source) {
	$conn = getDBConnection();
	$result = $conn->query("select * from dictionary_term_definitions where mergedby = ? and comefrom = ? limit 1", array($mergedto, $source));
	$set = 0;
	if($row = &$result->fetchRow(DB_FETCHMODE_ASSOC)) {
		$set = $row;
	}
	$conn->query("update dictionary_term_definitions set mergedby=NULL where serial = ? limit 1", array($set['serial']));
	$result->free();
	$conn->disconnect();
	return $set;
}
function service_dictionary_definition_setorder($serial, $order) {
	$conn = getDBConnection();
	$conn->query("update dictionary_term_definitions set inorder=? where serial=? limit 1", array($order, $serial));
	$conn->disconnect();
}
function service_dictionary_term_search($query, $belongto, $start, $size, $orderBy = "term") {
    $conn = getDBConnection();
	
    $result = $conn->query("select * from dictionary_terms where (serial in (
		select term from dictionary_term_definitions where definition like ?) or term like ?) and belongto = ?
												order by ".$orderBy." limit ?,? ", array("%".$query."%", "%".$query."%", $belongto,
												     $start, $size));
    $set = array();
    while ($row = &$result->fetchRow(DB_FETCHMODE_ASSOC)) {
		$row['definition'] = service_dictionary_getDefinition($row['serial']);
        array_push($set, $row);
    }
	
	$countResult = $conn->query("select count(serial) from dictionary_terms where (serial in (
		select term from dictionary_term_definitions where definition like ?) or term like ?) and belongto = ?", array("%".$query."%", "%".$query."%", $belongto));
    $count = 0;
    if ($row = &$countResult->fetchRow(DB_FETCHMODE_ASSOC)) {
        $count = $row['count(serial)'];
    }
	$result->free();
    $conn->disconnect();
    return array("terms"=>$set, "count"=>$count);
}
?>